﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace library
{
    public partial class bookColInfo : Form
    {
        public bookColInfo()
        {
            InitializeComponent();
        }
        string select_bookColId = "";

        private void return_btn_Click(object sender, EventArgs e)
        {
            this.Hide();
            Main frm = new Main();
            frm.Show();
        }

        protected void ClearTexBox()
        {
            ISBN_box.Text = "";
            bookColId_box.Text = "";
            int year = System.DateTime.Now.Year;
            int month = System.DateTime.Now.Month;
            int day = System.DateTime.Now.Day;
            InboundTime_box.Value = new DateTime(year, month, day);
          
        }

        private void btn_cancel_Click(object sender, EventArgs e)
        {
            ClearTexBox();
        }

        private void btn_save_Click(object sender, EventArgs e)
        {
            string bookColId = bookColId_box.Text.Trim();
            if (lbl_note.Text.Trim()=="添加"&&bookColId == "")
            {
                MessageBox.Show("输入馆藏编号");
                return;
            }
            string ISBN = ISBN_box.Text.Trim();
            if (lbl_note.Text.Trim() == "添加"&&ISBN == "")
            {
                MessageBox.Show("输入ISBN");
                return;
            }
            if (lbl_note.Text == "添加")
            {

                string sql = string.Format("INSERT INTO bookcollectioninfo (bookColId, status, InboundTime,ISBN) VALUES ('{0}', 0, '{1}', '{2}')", bookColId, InboundTime_box.Value.ToString("yyyy-MM-dd"), ISBN);
                MySqlConnection conn = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();

                string s = string.Format("update bookinfo set allInventory = allInventory + 1,inventory = inventory + 1 where ISBN = '{0}';",  ISBN);
                MySqlConnection c = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                c.Open();
                MySqlCommand cm = new MySqlCommand(s, c);
                int j = cm.ExecuteNonQuery();
                c.Close();

                if (j>0 && i > 0)
                {
                    MessageBox.Show("添加馆藏成功");
                    ClearTexBox(); //调用函数，清空各控件
                    DataBind_col();
                }
                else
                {
                    MessageBox.Show("添加馆藏失败");
                    return;
                }
            }
            else if (lbl_note.Text.Trim() == "修改")
            {
                MySqlConnection conn = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                conn.Open();

                string str = string.Format("update bookcollectioninfo set InboundTime = '{0}' where bookColId = '{1}';",InboundTime_box.Value.ToString("yyyy-MM-dd"), select_bookColId);
                MySqlCommand cmd = new MySqlCommand(str, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                if (i > 0)
                {
                    MessageBox.Show("信息修改成功！");
                    DataBind_col(); 
                }
                else
                {
                    MessageBox.Show("信息修改失败！");
                }
                lbl_note.Text = "添加";
                ClearTexBox();
            }
        }

        private void bookColInfo_Load(object sender, EventArgs e)
        {
            DataBind_col();
            int year = System.DateTime.Now.Year;
            int month = System.DateTime.Now.Month;
            int day = System.DateTime.Now.Day;
            InboundTime_box.Value = new DateTime(year, month, day);
        }

        protected void DataBind_col()
        {
            MySqlConnection conn = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
            conn.Open();
            string str = "select * from bookcollectioninfo";
            MySqlDataAdapter da = new MySqlDataAdapter(str, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            listView1.Items.Clear();
            foreach (DataRow dr in dt.Rows)
            {
                ListViewItem myitem = new ListViewItem(dr["bookColId"].ToString());
                if (dr["status"].ToString() == "0")
                {
                    myitem.SubItems.Add("在库");
                }
                else if (dr["status"].ToString() == "1")
                {
                    myitem.SubItems.Add("已借出");
                }
                else if (dr["status"].ToString() == "2")
                {
                    myitem.SubItems.Add("已丢失");
                }
                myitem.SubItems.Add(dr["InboundTime"].ToString());
                myitem.SubItems.Add(dr["ISBN"].ToString());
                listView1.Items.Add(myitem); //将新建项添加到 ListView 控件中
            }

        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listView1.SelectedItems.Count > 0) //如果有选中项
            {
                ListViewItem myitem = listView1.SelectedItems[0]; //获取选中的第一行（一次只能选一行）
                int year = Convert.ToInt32(myitem.SubItems[2].Text.Split(' ')[0].Split('/')[0]);
                int month = Convert.ToInt32(myitem.SubItems[2].Text.Split(' ')[0].Split('/')[1]);
                int day = Convert.ToInt32(myitem.SubItems[2].Text.Split(' ')[0].Split('/')[2]);
                InboundTime_box.Value = new DateTime(year, month, day);
                lbl_note.Text = "修改";
                select_bookColId = myitem.SubItems[0].Text;
            }
        }

        private void delete_btn_Click(object sender, EventArgs e)
        {
            if (select_bookColId == "") //如果没有选中要删除的客户信息
            {
                MessageBox.Show("请先选择要删除的馆藏");
            }
            else
            {
                //弹出删除确认提示框
                DialogResult result = MessageBox.Show("确定要删除选中的馆藏吗？", "删除提示",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes) //如果确定删除
                {
                    MySqlConnection conn = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                    conn.Open();
                    string str = string.Format("select * from bookcollectioninfo where bookColId ='{0}'", select_bookColId);
                    MySqlDataAdapter da = new MySqlDataAdapter(str, conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    conn.Close();
                    DataRow dr = dt.Rows[0];
                    string select_isbn = dr["ISBN"].ToString();
                    if (Convert.ToInt32(dr["status"].ToString()) > 0)
                    {
                        MessageBox.Show("该书已外借或丢失，不能删除");
                        return;
                    }
                    MySqlConnection c = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                    c.Open();
                    string sql = string.Format("delete from bookcollectioninfo where bookColId ='{0}'", select_bookColId);
                    MySqlCommand cmd = new MySqlCommand(sql, c);
                    int i = cmd.ExecuteNonQuery();
                    c.Close();

                    MySqlConnection co = new MySqlConnection("server=localhost; database=librarysystem; UID=root; PWD = 123456; port = 3306");
                    co.Open();
                    string s = string.Format("update bookinfo set allInventory = allInventory - 1,inventory = inventory -1   where ISBN ='{0}'", select_isbn);
                    MySqlCommand cm = new MySqlCommand(s, co);
                    int j = cm.ExecuteNonQuery();
                    c.Close();
                    if (j>0 && i > 0)
                    {
                        MessageBox.Show("删除成功");
                        ClearTexBox();
                        DataBind_col();
                    }
                    else
                    {
                        MessageBox.Show("信息删除失败！");
                    }
                    select_bookColId = "";
                }
            }
        }
    }
}
